package test.db.table.oracle;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;

import org.junit.After;
import org.junit.Before;
import org.junit.ComparisonFailure;
import org.junit.Test;
import org.junit.runners.Suite.SuiteClasses;

import test.db.table.Table;
import test.db.util.DBUtil;

/**
 * @author newbie
 * @since 0.1
 */
@SuiteClasses( { OracleDb.class })
public class OracleDb {
    private Connection con = null;

    @Before
    public void connect() {
        con = DBUtil.getConnection();

        assertNotNull(con);
    }

    @Test
    public void tableValidator() throws Exception {
        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = con.prepareStatement("SELECT * FROM TAB WHERE TNAME LIKE 'PW%'");
            rs = pstmt.executeQuery();

            while (rs.next()) {
                assertEquals(rs.getString(1).toUpperCase(), OracleTableList.valueOf(rs.getString(1).toUpperCase()).getTableName());

                count++;
            }

            assertEquals(count, OracleTableList.values().length);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assertTrue(DBUtil.releaseResultSet(rs));
            assertTrue(DBUtil.releasePreparedStatement(pstmt));
        }
    }
    
    @Test
    public void tableStructureValidator() throws Exception {
        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String currentTableName = null;
        Map<String, String> map = new HashMap<String, String>();

        try {
            OracleTableList[] tableList = OracleTableList.values();

            int totalCount = tableList.length;
            
            StringBuilder sb = new StringBuilder();
            
            for (int i = 0; i < totalCount; i++) {
                sb.delete(0, sb.length());
                
                currentTableName = tableList[i].getTableName().toUpperCase();

                sb.append("SELECT CNAME");
                sb.append("     , CASE COLTYPE");
                sb.append("       WHEN 'NUMBER' THEN COLTYPE || '(' || PRECISION || ')'");
                sb.append("       WHEN 'CLOB' THEN COLTYPE");
                sb.append("       ELSE COLTYPE || '(' || WIDTH || ')'");
                sb.append("       END CASE");
                sb.append("     , NULLS");
                sb.append("  FROM COL");
                sb.append(" WHERE TNAME = '").append(currentTableName).append("'");
                
                pstmt = con.prepareStatement(sb.toString());
                rs = pstmt.executeQuery();

                Class<? extends Object> clazz = Class.forName("test.db.table.oracle.type." + parseTableName(currentTableName) + "Table");
                Object[] obj = clazz.getEnumConstants();
                
                map.clear();

                for (Object object : obj) {
                    map.put(object.toString(), ((Table) object).getType());
                }

                int columnCount = 0;

                while (rs.next()) {
                    String type = map.get(rs.getString(1).toUpperCase());
                    System.out.println(type + " = " + rs.getString(2).toUpperCase() + " -> " + rs.getString(1));
                    if (type != null)
                        assertEquals(rs.getString(2).toUpperCase(), type);
                    else
                        throw new Exception();

                    columnCount++;
                }

                if (columnCount > map.size() || columnCount < map.size())
                    throw new Exception();

                count++;
            }
            
            assertEquals(count, totalCount);
        } catch (ComparisonFailure e) {
            initTable(currentTableName);
            
            assertTrue(false);
        } catch (Exception e) {
            e.printStackTrace();
            
            initTable(currentTableName);
            
            assertTrue(false);
        } finally {
            assertTrue(DBUtil.releaseResultSet(rs));
            assertTrue(DBUtil.releasePreparedStatement(pstmt));
        }
    }
    
    private void initTable(String tableName) throws Exception {
        System.out.println(tableName);
        
        DBUtil.dropTable(tableName, DBUtil.TYPE.ORACLE);
        DBUtil.createTable(OracleTableList.valueOf(tableName).getQuery(), tableName, DBUtil.TYPE.ORACLE);
        OracleTableList.valueOf(tableName).initData();
    }
    
    private String parseTableName(String tableName) {
        String result = null;
        
        StringTokenizer st = new StringTokenizer(tableName.toLowerCase(), "_");
        StringBuilder sb = new StringBuilder();
        
        while(st.hasMoreTokens()) {
            String temp = st.nextToken();
            
            
            for(int i=0; i<temp.length(); i++) {
                if(i == 0) {
                    char t = temp.charAt(i);
                    
                    sb.append((t + "").toUpperCase());
                    
                } else {
                    sb.append(temp.charAt(i));
                }
            }
        }
        
        result = sb.toString(); 
        
        return result;
    }

    @After
    public void disconnection() throws SQLException {
        assertTrue(DBUtil.releaseConnection(con));
        assertTrue(con.isClosed());

        if (con.isClosed()) {
            con = null;
        }

        assertNull(con);
    }
}
